<?php

/*
 *  Sep. 2, version for inner join function
 */

class MysqlAccess {

    private $dbConfig_array;
    private $query_stmt;
    private $select_stmt;
    private $select_part;
    private $update_part;
    private $delete_part;
    private $insert_part;
    private $join_part;
    private $join_on_part;
    private $where_part;
    private $like_part;
    private $order_part;
    private $limit_part;
    private $keywords;
    private $query_type;
    private static $conn;

    function __construct() {
        $Config = Config::getInstance();
        $this->dbConfig_array = $Config->getDBConfig();
        $this->setKeywords();
    }

    function getInstance() {
        if (self::$conn) {
            return self::$conn;
        }

        self::$conn = mysqli_init();
        $success = mysqli_real_connect(self::$conn, $this->dbConfig_array['host'], $this->dbConfig_array['id'], $this->dbConfig_array['pwd'], $this->dbConfig_array['database'], $this->dbConfig_array['port']);
        return self::$conn;
    }

    /*
     * array (op of 'AND' or 'OR', array (op of 'like' or '=', array of (column => value)))
     * EX: array("AND"=>array("="=>array(field1=>value1, field2=>value2), ">"=>array(field3=>value3)))
     *     array(""=>array("="=>array(field1=>value1)))
     * if operators is null, all operators are "AND"
     * 
     * if it is after a inner join, should use "table.field1=>value1"
     * 
     */

    public function where($condition) {
        $this->where_part = "";
        $ops = array_keys($condition);

        if (empty($ops[0])) {
            //NO "AND", "OR" 
            $keys = array_keys($condition[$ops[0]]);
            $opr = $keys[0];
            $field_array = $condition[$ops[0]][$opr];
            $sub_keys = array_keys($field_array);
            $condition[$ops[0]][$opr][$sub_keys[0]] = $this->mysql_escape($condition[$ops[0]][$opr][$sub_keys[0]]);
            $sub_keys[0]= $this->mysql_escape($sub_keys[0]);
            $this->where_part = $this->where_part
                    . " " . $sub_keys[0] . " " . $opr . " '" . $condition[$ops[0]][$opr][$sub_keys[0]] . "' ";
        } else {   //Multiple Join Conditions
            $first_one = TRUE;
            foreach ($ops as $index => $op) {
                if (!empty($condition[$op]) && count($condition[$op]) > 0) {
                    foreach ($condition[$op] as $mopr => $fv_pair) {
                        $mkeys = array_keys($fv_pair);

                        foreach ($mkeys as $idx => $mfield) {
                            $mfield= $this->mysql_escape($mfield);
                            $fv_pair[$mfield]= $this->mysql_escape($fv_pair[$mfield]);
                            if ($first_one) {
                                if ($mopr == "IN" || $mopr == "OR") {
                                    $this->where_part = $this->where_part . " `" . $mfield . "` " . $mopr . " (" . $fv_pair[$mfield] . ") ";
                                } else {
                                    $this->where_part = $this->where_part . " `" . $mfield . "` " . $mopr . " '" . $fv_pair[$mfield] . "' ";
                                }
                                $first_one = FALSE;
                            } else {
                                if ($mopr == "IN" || $mopr == "OR") {
                                    $this->where_part = $this->where_part . strtoupper($op) . " `" . $mfield . "` " . $mopr . " (" . $fv_pair[$mfield] . ") ";
                                } else {
                                    $this->where_part = $this->where_part . strtoupper($op) . " `" . $mfield . "` " . $mopr . " '" . $fv_pair[$mfield] . "' ";
                                }
                            }
                        }
                    }
                }
            }
        }

        $this->where_part = empty($this->where_part) ? '' : ' WHERE ' . $this->where_part;
        return $this->where_part;
    }

    public function putAndLikeBoth($like_array) {
        $this->like_part = '';
        $like_array = $this->mysql_escape($like_array);
        $i = 0;
        if (is_array($like_array) && count($like_array) > 0)
            foreach ($like_array as $key => $value) {
                if ($i > 0) {
                    $this->like_part .= " AND ";
                }
                $this->like_part .= "`" . $key . "`" . " LIKE '%" . $value . "%'";
                $i++;
            }
        return $this->like_part;
    }

    public function putORLikeBoth($like_array) {
        $like_array = $this->mysql_escape($like_array);
        $i = 0;
        $this->like_part = '';
        if (!empty($like_array) && count($like_array) > 0)
            foreach ($like_array as $key => $value) {
                if ($i > 0)
                    $this->like_part .= " OR ";
                $this->like_part .="`" . $key . "`" . " LIKE '%" . $value . "%'";
                $i++;
            }

        return $this->like_part;
    }

    public function innerJoin($tables) {
        //INNER JOIN messages INNER JOIN languages
        $tables = $this->mysql_escape($tables);

        foreach ($tables as $index => $tbl) {
            if ($index == 0) {
                $this->join_part = " INNER JOIN `" . $tbl . "`";
            } else {
                $this->join_part = $this->join_part . " INNER JOIN `" . $tbl . "`";
            }
        }
    }

    /*
     * conditions represent 
     * Ex: array ("" => array(array("=", table1=>field1, table2=>field2)))
     *     array ("AND" => array(array("=", table1=>field1, table2=>field2), array("<>", table3=>field3, table2=>field2)
     *                   , array("<>", table4=>field4, table3=>field3)), 
     *              "OR"=> array(array("=", table5=>field5, table6=>field6)))
     * operators represent "AND",  "OR" its squence matters.
     * if operators is null, all operators are "AND"
     * 
     * SELECT * FROM `event` INNER JOIN `event_report` INNER JOIN `member` 
     * ON `table1`.`field1` = `table2`.`field2`AND `table3`.`field3` <> `table2`.`field2`AND `table4`.`field4` <> `table3`.`field3`
     * OR `table5`.`field5` = `table6`.`field6` LIMIT 0, 10
     * 
     */

    public function joinOn($condition) {
        $this->join_on_part = " ON ";
        $ops = array_keys($condition);

        if (empty($ops[0])) {
            //NO "AND", "OR" 
            $keys = array_keys($condition[$ops[0]][0]);
            $opr = $condition[$ops[0]][0][0];
            $table1 = $keys[1];
            $table2 = $keys[2];
            $this->join_on_part = $this->join_on_part
                    . " `" . $table1 . "`.`" . $condition[$ops[0]][0][$table1] . "` " . $opr
                    . " `" . $table2 . "`.`" . $condition[$ops[0]][0][$table2] . "`";
        } else {   //Multiple Join Conditions
            if ((count($ops) == 1)) {
                $op = $ops[0];
                $tf_pairs = $condition[$op];
                if (count($tf_pairs) == 1) {
                    $tf_pair = $tf_pairs[0];
                    $mkeys = array_keys($tf_pair);
                    $mopr = $tf_pair[0];
                    $mtable1 = $mkeys[1];
                    $mtable2 = $mkeys[2];
                    $this->join_on_part = $this->join_on_part
                            . " `" . $mtable1 . "`.`" . $tf_pair[$mtable1] . "` " . $mopr
                            . " `" . $mtable2 . "`.`" . $tf_pair[$mtable2] . "`";
                    return $this->join_on_part;
                }
                foreach ($tf_pairs as $idx => $tf_pair) {
                    if (count($tf_pairs) - 1 == $idx) {
                        $mkeys = array_keys($tf_pair);
                        $mopr = $tf_pair[0];
                        $mtable1 = $mkeys[1];
                        $mtable2 = $mkeys[2];
                        $this->join_on_part = $this->join_on_part
                                . " `" . $mtable1 . "`.`" . $tf_pair[$mtable1] . "` " . $mopr
                                . " `" . $mtable2 . "`.`" . $tf_pair[$mtable2] . "`";
                    } else {
                        $mkeys = array_keys($tf_pair);
                        $mopr = $tf_pair[0];
                        $mtable1 = $mkeys[1];
                        $mtable2 = $mkeys[2];
                        $this->join_on_part = $this->join_on_part
                                . " `" . $mtable1 . "`.`" . $tf_pair[$mtable1] . "` " . $mopr
                                . " `" . $mtable2 . "`.`" . $tf_pair[$mtable2] . "`" . $op;
                    }
                }
                return $this->join_on_part;
            }
            foreach ($ops as $index => $op) {
                $tf_pairs = $condition[$op];
                if (count($tf_pairs) == 1 && $index > 0) {
                    $tf_pair = $tf_pairs[0];
                    $mkeys = array_keys($tf_pair);
                    $mopr = $tf_pair[0];
                    $mtable1 = $mkeys[1];
                    $mtable2 = $mkeys[2];
                    $this->join_on_part = $this->join_on_part . $op
                            . " `" . $mtable1 . "`.`" . $tf_pair[$mtable1] . "` " . $mopr
                            . " `" . $mtable2 . "`.`" . $tf_pair[$mtable2] . "`";
                } elseif (count($tf_pairs) > 1) {
                    foreach ($tf_pairs as $idx => $tf_pair) {
                        if (count($tf_pairs) - 1 == $idx) {
                            $mkeys = array_keys($tf_pair);
                            $mopr = $tf_pair[0];
                            $mtable1 = $mkeys[1];
                            $mtable2 = $mkeys[2];
                            $this->join_on_part = $this->join_on_part
                                    . " `" . $mtable1 . "`.`" . $tf_pair[$mtable1] . "` " . $mopr
                                    . " `" . $mtable2 . "`.`" . $tf_pair[$mtable2] . "`";
                        } else {
                            $mkeys = array_keys($tf_pair);
                            $mopr = $tf_pair[0];
                            $mtable1 = $mkeys[1];
                            $mtable2 = $mkeys[2];
                            $this->join_on_part = $this->join_on_part
                                    . " `" . $mtable1 . "`.`" . $tf_pair[$mtable1] . "` " . $mopr
                                    . " `" . $mtable2 . "`.`" . $tf_pair[$mtable2] . "`" . $op;
                        }
                    }
                }
            }
        }
    }

    public function select($columns, $table, $distinct = 0) {
        $this->query_type = "SELECT";
        if ($distinct == 0) {
            $this->select_part = 'SELECT ';
        } else {
            $this->select_part = 'SELECT DISTINCT';
        }
        $columns = $this->mysql_escape($columns);
        $table = $this->mysql_escape($table);

        foreach ($columns as $index => $col) {
            if ($index == count($columns) - 1) {
                $this->select_part = $this->select_part . $col . " FROM `" . $table . "`";
            } else {
                $this->select_part = $this->select_part . $col . ", ";
            }
        }
    }

    /*
     * $table @string : the name of the table
     * $columns @array : the columns array(column_name => column_value, column_name1 => column_value1)
     */

    public function update($columns, $table) {
        $columns = $this->mysql_escape($columns);
        $table = $this->mysql_escape($table);
        $this->query_type = "UPDATE";
        $this->update_part = "UPDATE `" . $table . "` SET ";
        $size = count($columns) - 1;
        $n = 0;
        foreach ($columns as $column_index => $column_value) {

            if (in_array($column_value, $this->keywords)) {
                $this->update_part = $this->update_part . "`" . $column_index . "`=" . $column_value . ", ";
            } else {
                $this->update_part = $this->update_part . "`" . $column_index . "`='" . $column_value . "', ";
            }
            $n++;
        }
        $this->update_part = substr($this->update_part, 0, strrpos($this->update_part, ","));
    }

    /*
     * $table @string : the name of the table
     * $columns @array : the columns array(column_name => column_value, column_name1 => column_value1)
     * 
     * $keywords like TIMESTAMP, it needs to be taken care of 
     */

    public function insert($columns, $table) {
        $columns = $this->mysql_escape($columns);
        $table = $this->mysql_escape($table);
        $this->query_type = "INSERT";
        $this->insert_part = "INSERT INTO " . $table . " ( ";
        $size = count($columns) - 1;
        $n = 0;
        foreach ($columns as $column_index => $column_value) {
            if ($n == $size) {
                $this->insert_part = $this->insert_part . "`" . $column_index . "`) VALUES (";
            } else {
                $this->insert_part = $this->insert_part . "`" . $column_index . "`, ";
            }
            $n++;
        }

        $n = 0;
        foreach ($columns as $column_index => $column_value) {
            if ($n == $size) {
                if (in_array($column_value, $this->keywords)) {
                    $this->insert_part = $this->insert_part . $column_value . ")";
                } else {
                    $this->insert_part = $this->insert_part . "'" . $column_value . "')";
                }
            } else {
                if (in_array($column_value, $this->keywords)) {
                    $this->insert_part = $this->insert_part . $column_value . ", ";
                } else {
                    $this->insert_part = $this->insert_part . "'" . $column_value . "', ";
                }
            }
            $n++;
        }
    }

    /*
     * $table @string : the name of the table
     */

    public function delete($table) {
        $table = $this->mysql_escape($table);
        $this->query_type = "DELETE";
        $this->delete_part = "DELETE FROM " . $table;
    }

    /*
     *  $offset @int
     *  $interval @int
     */

    public function limit($offset, $interval) {

        $offset = (!is_null($offset)) ? $this->mysql_escape($offset) : $offset;
        $interval = $this->mysql_escape($interval);
        if (!is_null($offset)) {
            $this->limit_part = $this->limit_part . " LIMIT " . trim($offset) . ", " . trim($interval);
        } else {
            $this->limit_part = $this->limit_part . " LIMIT " . trim($interval);
        }
    }

    /*
     *  $column @string: column name in the database
     *  $if_desc @int: null or 1
     */

    public function orderBy($column, $if_desc = NULL) {
        $column = $this->mysql_escape($column);
        if ($if_desc == NULL) {
            $this->order_part = $this->order_part . " ORDER BY " . $column;
        } else {
            $this->order_part = $this->order_part . " ORDER BY " . $column . ' ' . $if_desc;
        }
    }

    public function execute() {

        $query_stmt = $this->getStatement();
        $then = microtime();

        $con = $this->getInstance();
        mysqli_set_charset($con, $this->dbConfig_array['encoding']);

        if (!$con) {
            die('Could not connect: ' . mysqli_error($con));
        }
        //echo $query_stmt.'<br/>';
        $mysql_results = mysqli_query($con, $query_stmt);

        if (!$mysql_results) {
            die('Could not query:' . mysqli_error($con));
        }
        if ($this->query_type == "INSERT") {
            $mysql_results = mysqli_insert_id($con);
        }

        $now = microtime();

        $this->cleanAll();

        //mysqli_close($con);

        return $mysql_results;
    }

    public function elmexecute($field) {
        //查原券是否未被领取
        try {
            //$conn = mysqli_init();
            $conn=mysqli_connect( $this->dbConfig_array['host'], $this->dbConfig_array['id'], $this->dbConfig_array['pwd'], $this->dbConfig_array['database']);

            mysqli_set_charset($conn, $this->dbConfig_array['encoding']);

            if (!$conn) {
                die('Could not connect: ' . mysqli_error($conn));
            }
            mysqli_autocommit($conn, FALSE); // i.e., start transaction

            $query = "SELECT * FROM orders WHERE ph_no='".$field['ph_no']."' AND shop_no='".$field['shop_no']."'  AND status=1 AND isdeleted=0 ";
            $result = mysqli_query($conn, $query);
            if ( !$result ) {
                $result->free();
            }

            if ($result->num_rows>0){
                return '';
            }

            $row = mysqli_fetch_array($result, MYSQLI_ASSOC);
            $this->insert($field, 'orders');
            $insert_sql = $this->getStatement();

            $result = mysqli_query($conn, $insert_sql);

            $id = mysqli_insert_id($conn);

            mysqli_commit($conn);
            mysqli_close($conn); // i.e., end transaction

            return $id;
        } catch (Exception $e) {

            // before rolling back the transaction, you'd want
            // to make sure that the exception was db-related
            mysqli_rollback($conn);
            mysqli_close($conn); // i.e., end transaction
            return '';
        }
    }




    /**
     * @return the $dbConfig_array
     */
    public function getDbConfig_array() {
        return $this->dbConfig_array;
    }

    /**
     * @return the $query_stmt
     */
    public function getStatement() {
        //Combine every part of the query statement 
        $this->where_part.=strlen($this->where_part) > 0 ? ( strlen($this->like_part) > 0 ? " AND  (" . $this->like_part . ")" : "") : ( strlen($this->like_part) > 0 ? " WHERE " . $this->like_part : '');

        switch ($this->query_type) {
            case "SELECT":
                $this->query_stmt = null;
                $this->query_stmt = $this->select_part . $this->join_part . $this->join_on_part
                        . $this->where_part . $this->order_part . $this->limit_part;
                break;
            case "UPDATE":
                $this->query_stmt = null;
                $this->query_stmt = $this->update_part . $this->where_part;
                break;
            case "INSERT":
                $this->query_stmt = null;
                $this->query_stmt = $this->insert_part;
                break;
            case "DELETE":
                $this->query_stmt = null;
                $this->query_stmt = $this->delete_part . $this->where_part;
                break;
        }
        return $this->query_stmt;
    }

    public function getSelectStatement() {
        if ($this->query_type != "SELECT") {
            return null;
        }
        $this->select_stmt = null;
        $this->select_stmt = $this->select_part . $this->join_part . $this->join_on_part
                . $this->where_part . $this->order_part . $this->limit_part;
        return $this->select_stmt;
    }

    public function cleanAll() {
        $this->query_type = "";
        $this->select_part = "";
        $this->join_part = "";
        $this->join_on_part = "";
        $this->where_part = "";
        $this->order_part = "";
        $this->limit_part = "";
        $this->update_part = "";
        $this->insert_part = "";
        $this->delete_part = "";
    }

    /**
     * @param field_type $dbConfig_array
     */
    public function setDbConfig_array($dbConfig_array) {
        $this->dbConfig_array = $dbConfig_array;
    }

    /**
     * @param field_type $query_stmt
     */
    public function setStatement($query_stmt) {
        $this->query_stmt = $query_stmt;
    }

    public function setKeywords() {
        $this->keywords['now()'] = "now()";
        $this->keywords['null'] = "null";
    }

    public function mysql_escape($content) {
        if (!get_magic_quotes_gpc()) {

            if (is_array($content)) {
                foreach ($content as $key => $value) {
                    $content[$key] = addslashes($value);
                }
            } else {
                $content = addslashes($content);
            }
        }

        return $content;
    }

    public function couponshareexecute($express_id,$SessionTel) {
      
        try {
            $conn=mysqli_connect( $this->dbConfig_array['host'], $this->dbConfig_array['id'], $this->dbConfig_array['pwd'], $this->dbConfig_array['database']);

            mysqli_set_charset($conn, $this->dbConfig_array['encoding']);

            if (!$conn) {
                die('Could not connect: ' . mysqli_error($conn));
            }
            mysqli_autocommit($conn, FALSE); // i.e., start transaction 不开启自动提交事务

            $query = "SELECT * FROM orders_express WHERE status = 2 AND id = $express_id AND isdeleted= 0";
            $result = mysqli_query($conn, $query);
            if ( !$result ) {
                $result->free();
            }

            if ($result->num_rows==0){
                return 300;
            }
            $OrdersExpressInfo = mysqli_fetch_array($result, MYSQLI_ASSOC);

            $ridersSQL = "SELECT * FROM riders WHERE status = 1 AND tel = $SessionTel AND isdeleted= 0";
            $ridersData = mysqli_query($conn, $ridersSQL);
            if ( !$ridersData ) {
                $ridersData->free();
            }

            if ($ridersData->num_rows==0){
                return 301;
            }
            $ridersInfo = mysqli_fetch_array($ridersData, MYSQLI_ASSOC);
            $express_mobile = $ridersInfo['tel'];
            $express_courier = $ridersInfo['name'];
            $status = 3;
            # 更新指派表
            $OrdersExpressEditSql = "update orders_express set express_mobile = $express_mobile , express_courier = '".$express_courier."' , status = $status where status = 2 and isdeleted = 0 and id = $express_id";
            $OrdersExpressEditS = mysqli_query($conn, $OrdersExpressEditSql);

            # 更新订单表 $OrdersExpressInfo['ph_no']
            $OrdersEditSql = "update orders set status = 3 where status = 2 and isdeleted = 0 and ph_no = '".$OrdersExpressInfo['ph_no']."'";
            $OrdersEditS = mysqli_query($conn, $OrdersEditSql);


            # 更新指派信息
            $ExpressDataSQL = "SELECT * FROM express_data WHERE shop_express_no = '".$OrdersExpressInfo['shop_express_no']."' AND isdeleted= 0";
            $ExpressData = mysqli_query($conn, $ExpressDataSQL);
            $ExpressInfo = mysqli_fetch_array($ExpressData, MYSQLI_ASSOC);

            if(is_array($ExpressInfo) && count($ExpressInfo) > 0){
                # 新增配送信息
                $status = 3;
                $shop_express_no = $OrdersExpressInfo['shop_express_no'];
                $express_order_no = $OrdersExpressInfo['order_no'];
                $express_type = $OrdersExpressInfo['express_type'];
                $t = date('Y-m-d H:i:s');

                $AddExpressDataSQL = "insert into express_data (status,shop_code,shop_code_desc,shop_express_no,express_type,createdt,express_code,express_code_desc,express_result,express_no) values ($status,$status,'派单中','".$shop_express_no."','".$express_type."','".$t."',200,'自建骑手抢单成功','OK','".$express_order_no."')";
                $ExpressData = mysqli_query($conn, $AddExpressDataSQL);
            }

            mysqli_commit($conn);
            mysqli_close($conn); // i.e., end transaction

            return 200;
        } catch (Exception $e) {

            // before rolling back the transaction, you'd want
            // to make sure that the exception was db-related
            mysqli_rollback($conn);
            mysqli_close($conn); // i.e., end transaction
            return '309';
        }
    }
}

?>
